with table_base as (
    select waybill_no                                                                                                --运单
         , first_code                                                                                                --一段码
         , second_code                                                                                               --二段码
         , third_code                                                                                                --三段码
         , send_station_number                                                                                       --站点顺序
         , send_station_network_code                                                                                 --发件网点
         , send_station_network_type                                                                                 --'网点类型,1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他'
         , send_station_send_later_time                                                                              --最晚发件时间
         , sent_station_send_packagecode                                                                             --发件所属包号
         , coalesce(send_station_send_next_network_code,
                    send_station_loading_next_network_code)                      as send_station_next_station        --发件下一站
         , if(send_station_send_next_network_code is not null, send_station_send_next_network_type,
              send_station_loading_next_network_type)                            as send_station_next_statation_type --发件下一站类型
         , next_station_arrival_early_time                                                                           --下一站最早到车时间
         , coalesce(send_station_send_scan_user, send_station_loading_scan_user) as deliver_later_user               --最晚派件员
         , coalesce(send_station_send_scan_user_code,
                    send_station_loading_scan_user_code)                         as deliver_later_user_code          --最晚派件员code
         , instore_early_scantime                                                                                    --最早入库时间
         , instore_early_scansitecode                                                                                --最早入库网点
         , outstore_early_scantime                                                                                   --最早出库时间
         , outstore_early_scansitecode                                                                               --最早出库网点
         , sign_early_scantime                                                                                       --最早签收时间
         , sign_early_scansitecode                                                                                   --最早签收网点
         , case
               when (substr(sign_early_scantime, 1, 10) in
                     (date_add('{{ execution_date | cst_ds }}', -4), '{{ execution_date | cst_ds }}')
                   or substr(instore_early_scantime, 1, 10) in
                      (date_add('{{ execution_date | cst_ds }}', -4), '{{ execution_date | cst_ds }}')
                   or substr(outstore_early_scantime, 1, 10) in
                      (date_add('{{ execution_date | cst_ds }}', -4), '{{ execution_date | cst_ds }}'))
                   then 1
               else 0 end                                                        as t15signtime_falg                 --1代表t-1和t-5时效签收. 0非
    from jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
    where dt between date_add('{{ execution_date | cst_ds }}', -30) and date_add('{{ execution_date | cst_ds }}', 0)
      and (substr(send_station_send_later_time, 1, 10) between date_add('{{ execution_date | cst_ds }}', -4) and '{{ execution_date | cst_ds }}')
      and send_station_network_code is not null
),
--逻辑处理
--1取下一站点.则记录中有send_station_network_code,send_station_next_station,next_next_network_code.即中心,集散(网点),集散(网点).一个运单一条数据,剔除错扫,剔除问题件
     trace_waybill_next_base as (
         select date_time
              , waybill_no
              , first_code
              , second_code
              , third_code
              , send_station_network_code
              , send_station_network_type
              , send_station_send_later_time
              , sent_station_send_packagecode
              , send_station_next_station
              , send_station_next_statation_type
              , next_station_arrival_early_time
              , deliver_later_user
              , instore_early_scantime
              , instore_early_scansitecode
              , outstore_early_scantime
              , outstore_early_scansitecode
              , sign_early_scantime
              , sign_early_scansitecode
              , next_next_network_code
              , next_next_code_type
              , next_next_arrival_early_time
              , t15signtime_falg    as remark2
              , send_station_number as remark3
              , ''                  as remark4
              , ''                  as remark5
         from (
                  select substr(a.send_station_send_later_time, 1, 10)                                                                                                     as date_time
                       , a.waybill_no                                                                                                                                                                      --运单
                       , a.first_code                                                                                                                                                                      --一段码
                       , a.second_code                                                                                                                                                                     --二段码
                       , a.third_code                                                                                                                                                                      --三段码
                       , a.send_station_network_code                                                                                                                                                       --发件网点
                       , a.send_station_network_type                                                                                                                                                       --'网点类型,1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他'
                       , a.send_station_send_later_time                                                                                                                                                    --最晚发件时间
                       , a.sent_station_send_packagecode                                                                                                                                                   --发件所属包号
                       , a.send_station_next_station                                                                                                                                                       --发件下一站
                       , a.send_station_next_statation_type                                                                                                                                                --类型
                       , a.next_station_arrival_early_time                                                                                                                                                 --下一站最早到车时间
                       , concat(a.deliver_later_user, '/', a.deliver_later_user_code)                                                                                      as deliver_later_user           --最晚派件员
                       , a.instore_early_scantime                                                                                                                                                          --最早入库时间
                       , a.instore_early_scansitecode                                                                                                                                                      --最早入库网点
                       , a.outstore_early_scantime                                                                                                                                                         --最早出库时间
                       , a.outstore_early_scansitecode                                                                                                                                                     --最早出库网点
                       , a.sign_early_scantime                                                                                                                                                             --最早签收时间
                       , a.sign_early_scansitecode                                                                                                                                                         --早早签收网点
                       , b.send_station_next_station                                                                                                                       as next_next_network_code       --下下一站
                       , b.send_station_next_statation_type                                                                                                                as next_next_code_type          --下下一站类型
                       , b.next_station_arrival_early_time                                                                                                                 as next_next_arrival_early_time --下一站最早到车时间
                       , row_number()
                          over (partition by a.waybill_no, a.send_station_network_code order by a.send_station_send_later_time asc, b.next_station_arrival_early_time asc) as rn
                       , a.t15signtime_falg
                       , a.send_station_number
                  from table_base a
                           left join table_base b
                                     on a.waybill_no = b.waybill_no
                                         and a.send_station_next_station = b.send_station_network_code
              ) a
         where send_station_network_type = 2
           and send_station_next_statation_type in (3, 4)
           and rn = 1
           and date_time in (date_add('{{ execution_date | cst_ds }}', -4), '{{ execution_date | cst_ds }}')
     ),
     scaninfo_tms as (
--          select waybill_no   --运单号
--               , step         --全网点轨迹步数
--               , scan_type    --扫描类型
--               , network_code --网点编码
--               , network_type --网点类型
--               , packagecode  --所属包号
--          from (
--                   select waybill_no   --运单号
--                        , step         --全网点轨迹步数
--                        , scan_type    --扫描类型
--                        , network_code --网点编码
--                        , network_type --网点类型
--                        , packagecode  --所属包号
--                        , row_number() over (partition by waybill_no ,network_code,step order by scan_time desc ) as rn
--                   from jms_dwd.dwd_wide_rank_basic_scaninfo_tms_dt
--                   where dt between date_add('{{ execution_date | cst_ds }}', -30) and date_add('{{ execution_date | cst_ds }}', 0)
--                     and network_code is not null
--                     and network_type = '2'
--                     and scan_type = 'centerArrival'
--               ) a
--          where rn = 1
         select waybill_no
              , package_code as packagecode
              , scan_type
              , scan_time
              , scan_site_code
         from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', -30)
     ),
     trace_waybill_next as (
         select a.date_time
              , a.waybill_no
              , a.first_code
              , a.second_code
              , a.third_code
              , a.send_station_network_code
              , a.send_station_network_type
              , a.send_station_send_later_time
              , b.packagecode as sent_station_send_packagecode
              , a.send_station_next_station
              , a.send_station_next_statation_type
              , a.next_station_arrival_early_time
              , a.deliver_later_user
              , a.instore_early_scantime
              , a.instore_early_scansitecode
              , a.outstore_early_scantime
              , a.outstore_early_scansitecode
              , a.sign_early_scantime
              , a.sign_early_scansitecode
              , a.next_next_network_code
              , a.next_next_code_type
              , a.next_next_arrival_early_time
              , a.remark2
              , a.remark3
              , a.remark4
              , a.remark5
         from trace_waybill_next_base a
                  --                   left join scaninfo_tms b
--                             on a.waybill_no = b.waybill_no
--                                 and a.remark3 = b.step
--                                 and a.send_station_network_code = b.network_code
                  left join scaninfo_tms b
                            on a.waybill_no = b.waybill_no
                                and a.send_station_network_code = b.scan_site_code
                                and a.send_station_send_later_time > b.scan_time
     )
insert
overwrite
table
jms_dm.dm_tab_missrate_trace_waybill_next_dt
partition
(
dt
)
select *
     , '{{ execution_date | cst_ds }}' as dt
from trace_waybill_next
    distribute by pmod(hash(rand()), 200);
